package cn.zyy.dao;

import cn.zyy.entity.Student;
import cn.zyy.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;

/**
 * 学生持久化层
 */
public class StudentDaoImpl implements StudentDao {

    @Override
    //根据学生姓名查询学生ID
    public Student selectId(String stuName) {
        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner qrn = new QueryRunner(dataSource);
        String sql = "select stu_id as stuId from student where stu_name = ?";
        try {
            Student student = qrn.query(sql, new BeanHandler<>(Student.class), stuName);
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    //增加学生
    public Integer addStudent(Student student) {
        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner qrn = new QueryRunner(dataSource);
        String sql = "insert into student(stu_id,stu_name) values(?,?)";
        try {
            int line = qrn.update(sql, student.getStuId(), student.getStuName());
            return line;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    //根据id删除学生
    public Integer delStudent(Integer stu_id) {
        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner qrn = new QueryRunner(dataSource);
        String sql = "delete from student where stu_id = ?";
        try {
            int line = qrn.update(sql, stu_id);
            return line;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    //根据学生id和班级名称给学生分班（根据班级名称查询班级ID，然后根据学生id，更新学生的班级信息）
    public Integer updateStudent(Integer stuId, long classId) {
        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner qrn = new QueryRunner(dataSource);
        String sql = "update student set class_id = ? where stu_id = ?";
        try {
            int line = qrn.update(sql, classId, stuId);
            return line;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    //查询所有学生信息,要求显示对应班级名称
    public List<Student> findAll() {
        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner qrn = new QueryRunner(dataSource);
        String sql = "select s.stu_id, s.stu_name as stuName, s.class_id as classId, c.class_name as className from student s left join class c on s.class_id = c.class_id";
        try {
            List<Student> students = qrn.query(sql, new BeanListHandler<>(Student.class));
            return students;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
